#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
drop table if exists tmp.tmp_member_active;
create table tmp.tmp_member_active as
select t12.device_id
from
(
	select t1.device_id device_id
	from dws.dws_member_start_day t1
	join dws.dws_member_start_day t2
	on t1.device_id == t2.device_id
	where t1.dt == date_add('$do_date', -2)
) t12
join dws.dws_member_start_day t3
on t12.device_id == t3.device_id
where t3.dt = '$do_date';

insert overwrite table ads.ads_member_continue_active_count
partition(dt='$do_date')
select count(*) from tmp.tmp_member_active;
"

hive -e "$sql"

